"""
    @author RinMi
    @since  2024/6/13  
"""
import os
import re
import sqlparse
import pymysql
from dotenv import load_dotenv

TPL_REGEX = r'<tpl>.+?</tpl>'

db_info = {}


class SQL:
    sql_text = ''
    label_list = []
    origin_sql = ''
    table_list = []
    column_list = []
    template = """
<tpl>
    <choose>
        <when dbType='mysql' or dbType='h2'>
            &{--}
        </when>
    </choose>
</tpl>
    """

    def __init__(self, origin_sql):
        self.origin_sql = origin_sql

    def make_sql(self):
        res = self.template.replace('&{--}', self.sql_text)
        for label in self.label_list:
            res = res.replace('&{----}', label, 1)
        return res


def get_text():
    with open(file=os.getenv('db.file'), mode='r', encoding='utf-8') as file:
        text = file.read()
        file.close()
        return text


def create_sql(text):
    tpl_list = re.findall(TPL_REGEX, text, re.DOTALL)
    sql_list = [SQL(item) for item in tpl_list]
    for sql in sql_list:
        un_tpl_sql = re.sub(r'<.?tpl>', '', sql.origin_sql)
        label_list = re.findall(r'<.+?>\s*.*\s*</.+?>', un_tpl_sql, re.MULTILINE)
        sql_text = re.sub(r'<.+?>\s*.*\s*</.+?>', '&{----}', un_tpl_sql, re.MULTILINE)
        sql.label_list = label_list
        sql.sql_text = sql_text
    return sql_list


def extract_tables_columns(sql_list):
    for sql in sql_list:
        table_list = []
        res_list = re.findall(r'(from \s*\w+|join \s*\w+)', sql.sql_text)
        table_list.extend([re.sub(r'from|join|\s', '', item) for item in res_list])
        sql.table_list = table_list
        column_list = []
        column_list.extend(db_info[item] for item in table_list)
        column_list = set(item for sublist in column_list for item in sublist)
        sql.column_list = column_list


def restruct_label(sql_list):
    for sql in sql_list:
        label_list = []
        for label in sql.label_list:
            label_instance_list = re.findall('</?.*>', label)
            sql_instance = re.sub(r'</?.*>', '', label)
            placeholder_list = re.findall(r'#{.*}', sql_instance)
            for placeholder in placeholder_list:
                sql_instance = sql_instance.replace(placeholder, str(placeholder).replace(' ', ''))
            for column in sql.column_list:
                sql_instance = re.sub(f' ?{column} ', f'`{column}` ', sql_instance)
            label_list.append(f'{label_instance_list[0]}{sql_instance}{label_instance_list[1]}')
        sql.label_list = label_list


def restruct_sql(sql_list):
    for sql in sql_list:
        parsed = sqlparse.parse(sql.sql_text)[0]
        text = ''
        for token in parsed.tokens:
            if isinstance(token, sqlparse.sql.Identifier):
                text = text + f'`{token.value}`'
            elif ',' in token.value:
                select = ','.join('`' + item + '`' for item in str(token.value).split(','))
                text = text + select
            elif 'where' in token.value:
                word = ''
                where = ''
                for char in str(token.value):
                    if char.isalpha() or char == '_':
                        word += char
                    else:
                        if len(char) != 0:
                            if word in sql.column_list:
                                where += f'`{word}`'
                            else:
                                where += word
                            word = ''
                        where += char
                text = text + where
            else:
                text = text + token.value
        sql.sql_text = text


def pack_sql(sql_list):
    return [item.make_sql() for item in sql_list]


def clear_label(text):
    return [re.sub(r'<.+?>\s*.*\s*</.+?>', '', item, re.MULTILINE) for item in text]


def write_text(text):
    with open(file=os.getenv('db.restruct'), mode='w', encoding='utf-8') as file:
        file.write(text)
        file.close()


def get_db_info():
    config = {
        'host': os.getenv('db.host'),
        'user': os.getenv('db.user'),
        'password': os.getenv('db.password'),
        'database': os.getenv('db.database'),
        'charset': os.getenv('db.charset')
    }
    connection = pymysql.connect(**config)
    try:
        with connection.cursor() as cursor:
            cursor.execute("SHOW TABLES;")
            tables = cursor.fetchall()
            for table in tables:
                cursor.execute(f'DESCRIBE {table[0]};')
                columns = cursor.fetchall()
                db_info[table[0]] = [str(column[0]) for column in columns]
    finally:
        connection.close()


if __name__ == '__main__':
    load_dotenv()
    get_db_info()
    content = get_text()
    s_list = create_sql(content)
    extract_tables_columns(s_list)
    restruct_sql(s_list)
    restruct_label(s_list)
    r_list = pack_sql(s_list)
    write_text('\n'.join(item for item in r_list))
